package cn.chainco.cts.idaoImpl;

import java.sql.SQLException;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.springframework.orm.hibernate3.HibernateCallback;

import cn.chainco.cts.idao.IEmplyeeDao;
import cn.chainco.cts.jdo.Company;
import cn.chainco.cts.jdo.Employee;
import cn.chainco.cts.jdo.User;

public class EmployeeDaoImpl extends GenericHibernateDaoImpl<Employee, Long> implements IEmplyeeDao{

	@SuppressWarnings("unchecked")
	public List<Employee> findEmployeeByCompany(final Company company){
		List<Employee> list = this.getHibernateTemplate().executeFind(new HibernateCallback(){
			public Object doInHibernate(Session _session) throws HibernateException, SQLException{
				Query query = _session.createQuery("from Employee e where e.company.id=? ");
				query.setParameter(0, company.getId());
				return query.list();
			}
		});

		return list;
	}

	@SuppressWarnings("unchecked")
	public List<Employee> findEmployeeByCompany(final Company company, final int type){
		List<Employee> list = this.getHibernateTemplate().executeFind(new HibernateCallback(){
			public Object doInHibernate(Session _session) throws HibernateException, SQLException{
				Query query = _session.createQuery("from Employee e where e.company.id=? and e.type=? ");
				query.setParameter(0, company.getId());
				query.setParameter(1, type);
				return query.list();
			}
		});

		return list;
	}

	@SuppressWarnings("unchecked")
	public Employee findEmployeeByUser(User user, Company company){
		String sql = "select * from cts_employee where 1=1 and userId=" + user.getId() + " and companyId=" + company.getId();
		List<Employee> employeelist = this.getSession().createSQLQuery(sql).addEntity(Employee.class).list();
		if(employeelist.size() > 0){
			return employeelist.get(0);
		}else{
			return null;
		}
	}

	/**
	 * 获取员工列表
	 */
	@SuppressWarnings("unchecked")
	public List<Employee> findEmployeByUser(User user, String status){
		String sql = "select * from cts_employee where 1=1 and userId=" + user.getId();
		if(status != null && !"".equals(status)){
			sql = sql + " and status in (" + status + ")";
		}

		sql += " order by type desc";
		List<Employee> list = this.getSession().createSQLQuery(sql).addEntity(Employee.class).list();
		return list;
	}

	public List<Employee> findEmployee(Company company , String type ,  String name , String status , String optype , String opval){
		String sql = "select * from cts_employee where companyId="+company.getId();
		
		if(name!=null && !name.trim().equals("")){
			sql += " and nickname like '%"+name+"%' ";
		}
		
		if(status!=null && !status.trim().equals("")){
			sql += " and status="+status;
		}
		
		if(optype!=null && !optype.trim().equals("") && opval!=null && !opval.trim().equals("")){
			if(optype.equals("1")){
				sql += " and option1="+opval;
			}else if(optype.equals("2")){
				sql += " and option2="+opval;
			}else if(optype.equals("3")){
				sql += " and option3="+opval;
			}else if(optype.equals("4")){
				sql += " and option4="+opval;
			}else if(optype.equals("5")){
				sql += " and option5="+opval;
			}
			
		}
		
		sql += " and type<>2 and type="+type;
		sql += " order by createTime desc";
		
		List<Employee> list = this.getSession().createSQLQuery(sql).addEntity(Employee.class).list();
		return list;
	}
	/**
	 * 删除员工和相关连的数据
	 * @param company
	 * @return
	 */
	@SuppressWarnings("unused")
	public int delEmployeeAll(Employee employee){
		int code = 0;
		try{
			String workmSql = "delete from cts_work  where employeeId=" + employee.getId();
			String workDocSql = "delete from cts_workdoc  where employeeId=" + employee.getId();
			String workFeeSql = "delete from cts_workfee  where employeeId1=" + employee.getId() + " or employeeId2=" + employee.getId();
			String workItemSql = "delete from cts_workitem  where serviceId=" + employee.getId();
			String workTeam = "delete from cts_workteam  where employeeId=" + employee.getId();
			delexecureDel(workmSql);
			code = 1;
		}catch(RuntimeException e){
			e.printStackTrace();
		}

		return code;
	}

	private int delexecureDel(String sql){
		Session session = getSession();
		Transaction transaction = session.beginTransaction();

		int code = 0;
		try{
			transaction = session.beginTransaction();
			Query q = session.createSQLQuery(sql);
			q.executeUpdate();
			transaction.commit();
			code = 1;
		}catch(HibernateException e){
			if(transaction != null){
				// 当发生异常时回滚事务，取消数据库操作
				transaction.rollback();
			}
			e.printStackTrace();
		}finally{
			releaseSession(getSession());
		}

		return code;
	}
}
